import re
import time
from flask import request
from flask_restful import abort
from flask_login import current_user, login_user
from funcy import project
from sqlalchemy.orm.exc import NoResultFound
from sqlalchemy.exc import IntegrityError
from funcy import partial

from redash import models, limiter
from redash.permissions import (
    require_permission,
    require_admin_or_owner,
    is_admin_or_owner,
    require_permission_or_owner,
    require_admin_default,
)
from redash.handlers.base import (
    BaseResource,
    require_fields,
    get_object_or_404,
    paginate,
    order_results as _order_results,
)

from redash.authentication.account import (
    invite_link_for_user,
    send_invite_email,
    send_password_reset_email,
    send_verify_email,
)
from redash.settings import parse_boolean
from redash import settings

class AttachesNewResource(BaseResource):
    # def get(self):
    #     result = models.Attaches.get_id()
    #     return result[0]

    @require_admin_default
    def post(self):
        req = request.get_json(force=True)
        # require_fields(req, ("code","name"))

        attaches = models.Attaches(
        org=self.current_org,
        userid=req["userid"],
        type = req["type"],
        annex =req["annex"],
        status =req["status"],
        sourcetype =req["sourcetype"],
        sourceid =req["sourceid"],
        )

        try:
            models.db.session.add(attaches)
            models.db.session.commit()
        except IntegrityError as e:
            # if "code" in str(e):
            #     abort(400, message="编号已存在.")
            abort(500)

        # timeserial.record_event(
        #     {"action": "create", "object_id": timeserial.id, "object_type": "timeserial"}
        # )

class AttachesResource(BaseResource):
    def get(self, source_id,source_type):
        result = models.Attaches.get_by_id(source_id,source_type)
        return result.to_dict()

    @require_admin_default
    def post(self, source_id,source_type):
        req = request.get_json(force=True)
        # require_fields(req, ("code","name"))
        try:
            attaches = models.Attaches.get_by_id(source_id,source_type)
            params = dict(annex=req["annex"])

            self.update_model(attaches, params)
            models.db.session.commit()
        except :
            attaches = models.Attaches(
            userid=req["userid"],
            org=self.current_org,
            type = req["sourcetype"],
            annex =req["annex"],
            status =0,
            sourcetype =req["sourcetype"],
            sourceid =req["sourceid"],
            )
            try:
                models.db.session.add(attaches)
                models.db.session.commit()
            except :
                abort(400, message="附件上传失败.") 
        # except IntegrityError as e:
        #     if "code" in str(e):
        #         abort(400, message="编号已存在.")
        #     abort(500)

    @require_admin_default
    def delete(self, source_id,source_type):
        attaches =  models.Attaches.get_by_id(source_id,source_type)
        models.db.session.delete(attaches)
        models.db.session.commit()

class AttachesListResource(BaseResource):
    def post(self):
        req = request.get_json(force=True)
        page = req["page"]
        page_size = req["pagesize"]
        sourceid = str(req["sourceid"])
        sourcetype = req["sourcetype"]
        strwhere ="1=1"
        # if sourceid !="0" :
        #     strwhere = strwhere + " and t1.sourceid = '"+sourceid+"'"
        # if sourcetype !='' :
        #     strwhere = strwhere + " and t1.name = '"+sourcetype+"'"

        strgetfield =""" t1.*"""

        tblname = """(select 1 AS id ,array_to_json(array(select annex From 
            (select JSON_ARRAY_ELEMENTS(annex) as annex From attaches where sourcetype='"""+sourcetype+"""' and sourceid="""+sourceid+""") aa where aa.annex->>'bytstatus'='0' and aa.annex->>'type' <>'lawyer' order by aa.annex ->> 'uid')) as uncheck,
            array_to_json(array(select annex From 
            (select JSON_ARRAY_ELEMENTS(annex) as annex From attaches where sourcetype='"""+sourcetype+"""' and  sourceid="""+sourceid+""") aa where aa.annex->>'bytstatus'='1' and aa.annex->>'type' <>'lawyer' order by aa.annex ->> 'uid')) as checking,
            array_to_json(array(select annex From 
            (select JSON_ARRAY_ELEMENTS(annex) as annex From attaches where sourcetype='"""+sourcetype+"""' and  sourceid="""+sourceid+""") aa where aa.annex->>'bytstatus'='2' and aa.annex->>'type' <>'lawyer' order by aa.annex ->> 'uid')) as checked,
            array_to_json(array(select annex From 
            (select JSON_ARRAY_ELEMENTS(annex) as annex From attaches where sourcetype='"""+sourcetype+"""' and  sourceid="""+sourceid+""") aa where aa.annex->>'type' ='lawyer' order by aa.annex ->> 'uid')) as lawyer) t1"""
        
        orderfield ="t1.id"
        result = models.ContractApply.get_pagequery(tblname,strgetfield,orderfield,page_size,page,0,strwhere)
        return result[0]

class AttachesListAllResource(BaseResource):
    def post(self):
        req = request.get_json(force=True)
        page = req["page"]
        page_size = req["pagesize"]
        annexname = req["annexname"]
        contractcode = req["contractcode"]
        contractname = req["contractname"]
        type = req["type"]
        userid = ''#str(req["userid"])
        orderfield = req["field"] if "field" in req else ""
        if orderfield=="contractcode":
            orderfield ="t2.code"
        elif orderfield=="contractname":
            orderfield ="t2.name"
        elif orderfield=="annexname":
            orderfield ="t1.annexname"
        elif orderfield=="partyaname":
            orderfield ="t3.strcustomername"
        elif orderfield=="typename":
            orderfield ="t4.typename"
        else:
            orderfield ="t1.id"
        order = req["order"] if "order" in req else 0

        strwhere ="t2.org_id=" +str(self.current_org.id)

        if annexname !='' :
            strwhere = strwhere + " and t1.annexname like '%"+annexname+"%'"
        if contractcode !='' :
            strwhere = strwhere + " and t2.code like '%"+contractcode+"%'"
        if contractname !='' :
            strwhere = strwhere + " and t2.name like '%"+contractname+"%'"
        
        if type !='' :
            strwhere = strwhere + " and  '"+type+"' = ANY(t2.type)"
        if userid !='':
            strwhere = strwhere + """ and  exists(select orgpower_ids from public.users where id='""" + userid + """' 
            and (t1.departmentid=any(orgpower_ids) or 0=any(orgpower_ids)))"""

        strgetfield =""" t1.id,t1.annexname,t1.annexurl,t2.code as contractcode,t2.name as contractname,t2.bill_date,
                t4.typename"""

        tblname = """(select cast(json_array_elements(annex)->>'name' as character varying(500)) as annexname,
            cast(json_array_elements(annex)->>'url' as character varying(500)) as annexurl,sourceid,sourcetype,replace(cast(uuid_generate_v4() as VARCHAR), '-', '') as id
            from public.attaches) t1  inner join public.contracts t2 on t1.sourceid=t2.id and t1.sourcetype='contracts'
            left join public.contracttypeview t4 on t2.type =t4.type  and t2.org_id=t4.org_id"""
        #orderfield ="t1.id"
        result = models.ContractApply.get_pagequery(tblname,strgetfield,orderfield,page_size,page,order,strwhere)
        return result[0]

class AttachesListSelResource(BaseResource):
    def post(self):
        req = request.get_json(force=True)
        sourcetype = req["sourcetype"] if "sourcetype" in req else ""
        sourceid = req["sourceid"] if "sourceid" in req else 0
        containpublic = req["containpublic"] if "containpublic" in req else 0
        userid = ''

        strsql ="""select t1.*
            from (select cast(json_array_elements(annex)->>'name' as character varying(500)) as annexname,
            cast(json_array_elements(annex)->>'uid' as character varying(500)) as uid,
            cast(json_array_elements(annex)->>'url' as character varying(500)) as annexurl,sourceid,sourcetype,id,org_id
            from public.attaches) t1  where t1.org_id=""" +str(self.current_org.id)
        if containpublic ==0 :
            strsql = strsql + " and t1.sourcetype = '"+sourcetype+"' and t1.sourceid = '"+str(sourceid)+"'"
        else :
            strsql = strsql + " and (t1.sourcetype = 'notice' or (t1.sourcetype = '"+sourcetype+"' and t1.sourceid = '"+str(sourceid)+"'))"
        strsql = """select array_to_json(array_agg(row_to_json(t))) from (""" + strsql + """) t """
        result = models.Contracts.get_selcontracts(strsql)
        return result[0]